package com.gjintel.business.proj.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.gjintel.business.proj.model.BidderChange;
import com.gjintel.framework.constant.ProjectStatus;
import org.springframework.stereotype.Repository;

import com.gjintel.business.proj.dao.IProjectBidDao;
import com.gjintel.business.proj.model.DepositReturn;
import com.gjintel.business.proj.model.ProjectBidInfo;
import com.gjintel.business.proj.model.ProjectInfo;
import com.gjintel.framework.register.model.DepositCategory;
import com.gjintel.sdk.base.dao.HibernateGenericDAO;
import com.gjintel.sdk.base.dao.Page;
import com.gjintel.sdk.util.StringUtil;

@Repository
public class ProjectBidDaoImpl extends HibernateGenericDAO implements IProjectBidDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getProjectBid(String projectId, String winningId) {
		String hql = "from ProjectBidInfo pb where pb.projectId=? and pb.userId =? and pb.flag=1 ";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		paramList.add(winningId);
		return (List<ProjectBidInfo>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getProjectBidCount(String projectId) {
		String hql = "from ProjectBidInfo pb where pb.projectId=? and pb.flag=1 order by pb.bidAmount asc, pb.bidTime asc,pb.millisecond asc";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		return (List<ProjectBidInfo>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getProjectBid(String projectId) {
		String hql = "select new com.gjintel.business.proj.model.ProjectBidInfo(p.id,p.userId,p.bidderName,p.projectId,p.bidTime,r.regisgterIdCard,p.consignorIdCard,r.mobile,p.consignorPhone,r.name,p.consignor,r.loginName)"
				+ "from ProjectBidInfo p ,RegisterUser r where p.userId = r.id and p.projectId = ? and p.flag=1 order by p.bidTime asc";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		return (List<ProjectBidInfo>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getProjectBidList(String projectId, Page page) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		paramList.add(projectId);
		String sql = "select pb.id as id, pb.bidder_name as bidderName,pb.bid_amount as bidAmount,pb.bank_back_time as bankBackTime,pb.deposit_status as depositStatus from t_Proj_Bid_Info pb, t_Proj_Info pi "
				+ " where pb.proj_Id =?  and pb.flag=1 " + " and pb.proj_Id=pi.id and pb.user_Id !=pi.winnning_Id  "
				+ " and pb.user_id not in" +
				// " and pb.id not in "+
				"(select dc.register_Id from t_sys_register_deposit dc,t_Proj_Info pi2 " +
				// " where dc.register_Id= pb.id and "+
				" where dc.register_Id= pb.user_id and " + " dc.deposit_categoryId = pi2.catagory_Id and pi2.id=? )";
		return this.findAsPageBySql(ProjectBidInfo.class, sql, page, null, null, null, false, paramList.toArray());

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getProjectBidByProjectId(String projectId) {
		String hql = "from ProjectBidInfo pb where pb.projectId =? and pb.flag=1 order by pb.bidAmount asc";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		return (List<ProjectBidInfo>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getProjectBidInfoById(String id) {
		String hql = "select new com.gjintel.business.proj.model.ProjectBidInfo(pb.userId,pb.bidderName,pb.projectId,pi.name)"
				+ "from ProjectBidInfo pb,ProjectInfo pi where pb.id=? and pb.projectId = pi.id and pb.flag=1 ";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(id);
		return (List<ProjectBidInfo>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositCategory> getDepositCategory(String winningId, String category) {
		String hql = "from DepositCategory dc where dc.registerId = ? and dc.depositCategoryId = ?";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(winningId);
		paramList.add(category);
		return (List<DepositCategory>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> getUnReturnList(String projectId, Integer depositStatus, String winningId) {
		String hql = "from ProjectBidInfo pb where pb.projectId =? and pb.depositStatus= ? and pb.userId=? and pb.flag=1 ";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		paramList.add(depositStatus);
		paramList.add(winningId);
		return (List<ProjectBidInfo>) this.find(hql, paramList.toArray());
	}

	@Override
	public List<ProjectBidInfo> getUnWinAndUnReturnList(String projectId, Integer depositStatus) {
		String sql = "select pb.id as id, pb.bidder_name as bidderName,pb.bid_amount as bidAmount,pb.bank_back_time as bankBackTime,pb.deposit_status as depositStatus from t_Proj_Bid_Info pb, t_Proj_Info pi "
				+ " where pb.proj_Id =?  and pb.flag=1 " + "and pb.deposit_status=?"
				+ " and pb.proj_Id=pi.id and pb.user_Id !=pi.winnning_Id  " +
				// " and pb.id not in"+
				" and pb.user_id not in" + "(select dc.register_Id from t_sys_register_deposit dc,t_Proj_Info pi2 "
				+ " where dc.register_Id= pb.user_id and " + " dc.deposit_categoryId = pi2.catagory_Id and pi2.id=? )";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		paramList.add(depositStatus);
		paramList.add(projectId);
		return this.findBySQL(ProjectBidInfo.class, sql, paramList.toArray());
	}

	@Override
	public int getProjectBidCountByProjIdAndEvalutionResult(String projectId, int evalutionResult) {
		String hql = "from ProjectBidInfo pb where pb.projectId=? and pb.evalutionResult=? and pb.flag=1 ";
		return this.getHQLCount(hql, projectId, evalutionResult);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositReturn> getDepositReturnList(String projectId) {
		String hql = "from DepositReturn dr where dr.pk.projectId = ? and dr.pk.depositType=0";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		return (List<DepositReturn>) this.find(hql, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositReturn> getUnBidDepositReturn(String projectId, String loginName, Page page, Integer type) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuffer hql = new StringBuffer("from DepositReturn dr where dr.pk.projectId=? and dr.pk.depositType=0 ");
		paramList.add(projectId);
		if (type == 1 && loginName != null ) { // 中标人
			hql.append("and dr.pk.companyName =? ");
			paramList.add(loginName);
		} else if(type == 1 && loginName == null) {
			return null;
		}else if(type == 0 &&  loginName != null){//非中标人
			hql.append("and dr.pk.companyName !=?");
			paramList.add(loginName);
		}
		return this.findAsPage(hql.toString(), page, paramList.toArray());
	}

	@Override
	public Integer getProjectCount(Date date) {
		List<Object> paramList = new ArrayList<Object>();
		String hql = "from ProjectInfo p where p.bidStartTime=? and p.status not in(26,6,16,24,15,4,29) and p.auditable=true";
		paramList.add(date);
		return this.getHQLCount(hql, paramList.toArray());
	}

	@Override
	public List<ProjectInfo> getProjectCodeCombo() {
		String hql = "from ProjectInfo p where (p.parentProjCode=null or p.parentProjCode='') and p.code!=null";
		return this.find(hql);
	}


	@Override
	public List<ProjectInfo> findListByWhere(ProjectInfo project, Date startTime, Date endTime) {
		StringBuilder hql = new StringBuilder("from ProjectInfo project where 1=1 ");
		hql.append(" and project.status not in(1,25,24,250)");//不查询待提交，审核不通过，撤销，流标的项目,超时未审核

		List<Object> paramList = new ArrayList<Object>();

		//业务数据条件
		if (project != null) {
			if (!StringUtil.isEmpty(project.getName())) {
				hql.append(" and project.name = ?");
				paramList.add(project.getName());
			}
		}

		if(startTime != null){
			hql.append(" and project.createTime >= ? ");
			paramList.add(startTime);
		}
		if(endTime != null){
			hql.append(" and project.createTime <= ? ");
			paramList.add(endTime);
		}

		return this.findAsPage(hql.toString(), null, null, "project", true, paramList.toArray());
	}


	@SuppressWarnings("unchecked")
	@Override
	public List<ProjectBidInfo> findValidBidInfo(ProjectInfo  pro,Page page) {
		String hql = "from ProjectBidInfo p where p.projectId =? and p.evalutionResult=1 and p.flag=1";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(pro.getId());
		if(pro!=null&&!pro.getAuditable()){
			hql+=" and p.isMultHit=true";
		}
		return this.findAsPage(hql, page, paramList.toArray());
	}

	@Override
	public ProjectBidInfo getBidderInfo(String projectId, String bidderId) {
		String hql = "from ProjectBidInfo p where p.projectId=? and p.userId=?";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(projectId);
		paramList.add(bidderId);
		return (ProjectBidInfo)this.findUnique(hql, paramList.toArray());
	}

	@Override
	public List<BidderChange> getBidderChanList(Page page) {
		String hql="select id,oldBidderId,oldBIdderName,newBidderId,newBidderName,projectId,resourceId,resourceName,updateTime,operatorId,operateName,changeReason,p.code as projectCode,p.name as projectName  from BidderChange b ,ProjectInfo p where b.projectId =p.id";
		return this.findAsPage(hql.toString(), page);
	}

	@Override
	public List<DepositReturn> getDepositReturnNoEvaluation(String projectId,Integer type) {
		List<Object> paramList = new ArrayList<Object>();
		//StringBuffer hql = new StringBuffer("select dr from DepositReturn dr,ProjectBidInfo pf where dr.pk.projectId=? and dr.pk.depositType=0 and dr.pk.projectId = pf.projectId and dr.bidderId = pf.userId");
		//StringBuffer hql = new StringBuffer("select dr from DepositReturn dr ,ProjectBidInfo pf where dr.pk.projectId = pf.projectId and dr.bidderId = pf.userId and dr.pk.projectId=? and dr.pk.depositType=0");

//		paramList.add(projectId);
//		if(type==0){
//		hql.append("and pf.isMultHit=false");
//		}else if(type==1){
//		hql.append("and pf.isMultHit=true");
		//}
		paramList.add(projectId);
		String hql = "select dr from DepositReturn dr where dr.pk.projectId=? and dr.pk.depositType=0";
		return this.find(hql,paramList.toArray());
	}
}
